import pandas as pd
import re

# 1. 读前 9 行，做颜色字典
palette = (
    pd.read_excel('G:\QQFile\Downloads\Reference.xls', nrows=9)
    .assign(color=lambda d: d['content']
            .astype(str)
            .str.extract(r'\[([0-9a-fA-F]{6})\]', expand=False))
    .dropna(subset=['color'])
    .set_index('index')['color']
    .to_dict()
)

print(palette)

# 2. 读取整张表（重新读即可，避免 nrows 截断）
df_all = pd.read_excel('G:\QQFile\Downloads\Reference.xls')


def replace_color_tag(text: str) -> str:
    """处理单个单元格"""
    if pd.isna(text):
        return text
    text = str(text)

    # 1) 收集所有 token
    tokens = [
        *[(m.start(), m.end(), 'open', int(m.group(1)))  # [n]
          for m in re.finditer(r'\[([1-9])\]', text)],
        *[(m.start(), m.end(), 'open', m.group(1))  # [6位色值]
          for m in re.finditer(r'\[([0-9a-fA-F]{6})\]', text)],
        *[(m.start(), m.end(), 'close', None)  # [-]
          for m in re.finditer(r'\[-\]', text)]
    ]
    tokens.sort(key=lambda t: t[0])  # 按起始排序

    # 2) 扫描并生成结果
    out, last = [], 0
    stack = []  # 保存 (开始下标, 颜色)

    for start, end, typ, val in tokens:
        out.append(text[last:start])  # 原文本
        last = end  # 直接跳到 token 结尾

        if typ == 'open':
            color = f'{val}' if isinstance(val, str) else palette[val]
            out.append(f'<color=#{color}>')
            stack.append(color)
        else:  # close
            if stack:
                out.append('</color>')
                stack.pop()

    out.append(text[last:])  # 剩余尾巴

    # 3) 补未闭合的标签
    out.extend(['</color>'] * len(stack))

    return ''.join(out)


# 3. 应用函数
df_all['content'] = df_all['content'].apply(replace_color_tag)

# 4. 保存结果
df_all.to_excel('G:\PythonTools\CheckColorTags\Reference.xlsx', index=False)

print('处理完成，已保存 Reference.xlsx')
